<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

$bar="Result of search by Salary";
        echo '<table class="tablebg" cellspacing="1" align="right" border="1" width="900px">';
        echo '<tr><td colspan=6 align=right class=bar>'.$bar.'</td></tr>';
        echo '<tr><td colspan=6 align=right class=bar>Total rows: '.$total_rows.'</td></tr>';
        //echo '<tr><td colspan=6 align=right class=bar>Time of : '.$FullName.'</td></tr>';
        echo '<tr><td colspan=6 align=right class=pagination>'.$pagination.'</td></tr>';
        echo '
              <th>starttime</th>
              <th>endtime</th>
              <th>overtime</th>
              <th>daytime</th>
              <th>weekday</th>
              <th>Action</th>';
        foreach($query->result() as $row){
            //$images='<img src="'.base_url().'application/templates/images/'.$row->staff_avarta.'" width=100 height=70>';
            //$details = anchor("user/loadUpdateForm/$row->Staff_ID",$images);
            echo "<tr>";
  
      
            echo "<td class=\"row1\">$row->starttime</td>"; 
            echo "<td class=\"row1\">$row->endtime</td>
                        <td class=\"row1\">$row->overtime</td>";
            if(isset($row->starttime))
            {
            echo "<td class=\"row1\">08:00</td>";
            }
            echo "<td class=\"row1\">$row->weekday</td></tr>";
            
            /*$titleUpdate = '<img src ="'.base_url().'templates/images/icons/edit.gif" title="Click here to modify"/>';
            $titleDelete = '<img src ="'.base_url().'templates/images/icons/delete.png" title="Click here to delete"/>';
            //$titleActive = '<img src ="'.base_url().'application/templates/images/icons/active.jpg" title="Click here to active"/>';
            $titleView = '<img src ="'.base_url().'templates/images/icons/view.gif" height=25 title="Click here to view "/>';
            $titleInsertFamily = '<img src ="'.base_url().'templates/images/icons/insert_family.jpeg" width=40 height=25 title="Click here to insert family"/>';
            $update = anchor("staff/loadStaffUpdateForm/$row->Staff_ID",$titleUpdate);
            $delete = anchor("staff/deleteStaff/$row->Staff_ID",$titleDelete);
            //$active = anchor("user/loadConfirmForm/$row->Staff_ID",$titleActive);
            $view_staff=anchor("staff/viewInformationStaff/$row->Staff_ID",$titleView);
            $insert_family=anchor("staff/loadListFamily/$row->Staff_ID",$titleInsertFamily);
            echo "<td class=\"row1\" align=\"center\">$update $delete $view_staff $insert_family</td></tr>";*/
            }
            /*$titleInsert = '<img src ="'.base_url().'templates/images/icons/addstaff.jpeg" width=60 height=40 title="Click here to insert staff"/>';
            $insert = anchor("staff/loadInterviewerPass",$titleInsert);
            echo '<tr><td colspan=5 align=center>'.$insert.'</td></tr>';
            echo '<tr><td colspan=5 align=right class=pagination>'.$pagination.'</td></tr>';*/
            $a=0;$b=0;$c=0;$d=0;$e=0;$f=0;$g=0;
            $i=0;$k=0;
            $gio=0;
            $phut=0;
            $gio1=0;
            $phut1=0;
         foreach ($query1->result() as $row1)
         {
             
             /*$phut=$phut+substr($row1->overtime,3,2);
             $gio=$gio+substr($row1->overtime,0,2);
             $phut1=$phut%60;
             $gio1=(int)($phut/60)+$gio;*/
             
             
            switch ($row1->weekday)
            {
                case 'Monday': {$a++;break;}
                case 'Tuesday': {$b++;break;}
                case 'Wednesday': {$c++;break;}
                case 'Thursday': {$d++;break;}
                case 'Firday': {$e++;break;}
                case 'Saturday': {$f++;break;}
                case 'Sunday': {$g++;break;}
            } 
         }
         $detail="Detail of workday";
         /*$h=$a+$b+$c+$d+$e+$f;
         $i=$gio1.':'.$phut1;
         //$k=$query1->num_rows()*8;*/
         
        echo '</table>';
        echo '<table class="tablebg" cellspacing="1" align="right" border="1" width="900px">';
        echo '<tr><td colspan=6 align=right class=bar>'.$detail.'</td></tr>';
        echo "<tr>
                <td class=row1>Numbers of Monday</td>
                <td class=row2>$a</td>
            </tr>
            <tr>
                <td class=row1>Numbers of Tuesday</td>
                <td class=row2>$b</td>
            </tr>
            <tr>
                <td class=row1>Numbers of Wednesday</td>
                <td class=row2>$c</td>
            </tr>
            <tr>
                <td class=row1>Numbers of Thursday</td>
                <td class=row2>$d</td>
            </tr>
            <tr>
                <td class=row1>Numbers of Firday</td>
                <td class=row2>$e</td>
            </tr>
            <tr>
                <td class=row1>Numbers of Saturday</td>
                <td class=row2>$f</td>
            </tr>
            <tr>
                <td class=row1>Numbers of Sunday</td>
                <td class=row2>$g</td>
            </tr>";
            /*echo " <tr>
                <td class='total'>Numbers of Workday</td>
                <td class='total'>$h</td>
            </tr>
            <tr>
                <td class='total'>Numbers hours overtime of Workday</td>
                <td class='total'>$i</td>
            </tr>
            <tr>
                <td class='total'>Numbers hours of Workday</td>
                <td class='total'>$k:00</td>
            </tr>";*/
            echo "<tr>
                <table class='tablebg' cellspacing='1' align='right' border='1' width='900px'>
                    
              <th>OT_Weekday</th>
              <th>OT_Saturday</th>
              <th>OT_Sunday</th>
              <th>overnight</th>
              
              <th>workedday</th>
              <th>workingday</th>
              <th>timesat</th>
              <th>timesun</th>";
            
        $hour=0;
        $minute=0;
        $hour1=0;
        $minute1=0;
        
        $hour2=0;
        $minute2=0;
        $hour3=0;
        $minute3=0;
        
        $hour4=0;
        $minute4=0;
        $hour5=0;
        $minute5=0;
        
        $overnight=0;
        $numbersunday=0;
        $numbersaturday=0;
        $labworkingday=0;
        $labworkedday=0;
            //echo "<tr><td class=total>$query1->num_rows</td>";
            //echo "<td class=total>$i</td>";
        $sql2="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime AND (
weekday = 'Monday'
OR weekday = 'Tuesday'
OR weekday = 'Wednesday'
OR weekday = 'Thursday'
OR weekday = 'Firday'
)
AND substr( overtime, 4, 2 ) !=0";
            $query2=$this->db->query($sql2);
            $a=0;$b=0;$c=0;$d=0;$e=0;$f=0;$g=0;
            $i=0;$k=0;
            $gio=0;
            $phut=0;
            $gio1=0;
            $phut1=0;
            foreach($query2->result() as $row2)
            {
                $phut=$phut+substr($row2->overtime,3,2);
                $gio=$gio+substr($row2->overtime,0,2);
                $phut1=$phut%60;
                $gio1=(int)($phut/60)+$gio;
            }
            $i=$gio1.':'.$phut1;
            
            echo "<tr><td class=\"row1\">$i</td>";
            
            
            $sql8="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime AND ( weekday = 'Saturday') AND substr( overtime, 4, 2 ) !=0";
            $query8=$this->db->query($sql8);
            
            $gio2=0;
            $phut2=0;
            $gio3=0;
            $phut3=0;
            foreach($query8->result() as $row8)
            {
                $phut2=$phut2+substr($row8->overtime,3,2);
                $gio2=$gio2+substr($row8->overtime,0,2);
                $phut3=$phut2%60;
                $gio3=(int)($phut2/60)+$gio2;
            }
            $i1=$gio3.':'.$phut3;
            
            echo "<td class=\"row1\">$i1</td>";
            
            $sql9="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime AND ( weekday = 'Sunday') AND substr( overtime, 4, 2 ) !=0";
            $query9=$this->db->query($sql9);
            
            $gio4=0;
            $phut4=0;
            $gio5=0;
            $phut5=0;
            foreach($query9->result() as $row9)
            {
                $phut4=$phut4+substr($row9->overtime,3,2);
                $gio4=$gio4+substr($row9->overtime,0,2);
                $phut5=$phut4%60;
                $gio5=(int)($phut4/60)+$gio4;
            }
            $i2=$gio5.':'.$phut5;
            
            echo "<td class=\"row1\">$i2</td>";
             $sql7="select count(*) as numberdayovernight from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and ((substr(starttime,12,2)>='21' and substr(endtime,12,2)<'24') or (substr(starttime,12,2)>'00' and substr(starttime,12,2)<'05'))";
            $query7=$this->db->query($sql7);
            foreach ($query7->result() as $row7)
            {
            echo "<td class=\"row1\">$row7->numberdayovernight</td>";
                
            }
            $sql3="select count(*) as numbersunday from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Sunday'";
            $query3=$this->db->query($sql3);
            foreach ($query3->result() as $row3)
            {
                //echo "<td class=\"row1\">$row3->numbersunday</td>";
            }
             $sql4="select count(*) as numbersaturday from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Saturday'";
            $query4=$this->db->query($sql4);
            foreach ($query4->result() as $row4)
            {
                //echo "<td class=\"row1\">$row4->numbersaturday</td>";
            }
            $sql5="select count(*) as numbersatall from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate'  and weekday='Saturday'";
            $query5=$this->db->query($sql5);
            foreach ($query5->result() as $row5)
            {
                //echo "<td class=\"row1\">$row5->numbersatall</td>";
            }
            $sql6="select count(*) as numbersundaya from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and weekday='Sunday'";
            $query6=$this->db->query($sql6);
            foreach ($query6->result() as $row6)
            {
                //echo "<td class=\"row1\">$row3->numbersunday</td>";
            }
            //echo $row4->numbersaturday;
            $workedday=$query1->num_rows-$row3->numbersunday+$row5->numbersatall-$row4->numbersaturday;
            $sql30="select count(distinct substr(starttime,1,10)) as totalday from ewt_works where starttime between '$fromdate' and '$todate'";
            $query30=$this->db->query($sql30);
            foreach ($query30->result() as $row30)
            {
                
            }
            $sql31="select count(distinct substr(starttime,1,10)) as totalsunday from ewt_works where weekday='Sunday' and starttime between '$fromdate' and '$todate'";
            $query31=$this->db->query($sql31);
            foreach ($query31->result() as $row31)
            {
                
            }
            $workingday=$row30->totalday-$row31->totalsunday;
            //$labworkingday=$labworkingday+$workingday;
            //$labworkedday=$labworkedday+$workedday;
            //echo "<td class=\"row1\">$labworkingday</td>";
            //$k=$workingday*8;
            echo "<td class=\"row1\">$workedday</td>";
            echo "<td class=\"row1\">$workingday</td>";
            $sql10="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Saturday'";
            $query10=$this->db->query($sql10);
            $hoursatturday=0;
            $minutesaturday=0;
            $hoursatturday1=0;
            $minutesaturday1=0;
            foreach($query10->result() as $row10)
            {
                 if(substr($row10->starttime,14,2)>substr($row10->endtime,14,2))
                {
                    $phutthu7=substr($row10->endtime,14,2)+60-substr($row10->starttime,14,2);
                    if((substr($row10->endtime,11,12)<=13||substr($row10->starttime,11,12)>=13)&&substr($row10->starttime,11,12)!=0)
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2)-1;
                    else {
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2)-2;
                    }
                }
                else
                {
                    $phutthu7=substr($row10->endtime,14,2)-substr($row10->starttime,14,2);
                    if((substr($row10->endtime,11,12)<=13||substr($row10->starttime,11,12)>=13)&&substr($row10->starttime,11,12)!=0)
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2);
                    else
                    {
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2)-1;
                    }
                }
                
                $minutesaturday=$minutesaturday+$phutthu7;
                $hoursatturday=$hoursatturday+$giothu7;
                $minutesaturday1=$minutesaturday%60;
                $hoursatturday1=(int)($minutesaturday/60)+$hoursatturday;
            }
            if($minutesaturday1<$phut3)
            {
                $minutesatday=$minutesaturday1+60-$phut3;
                $hoursatday=$hoursatturday1-1-$gio3;
            }
            else
            {
                $minutesatday=$minutesaturday1-$phut3;
                $hoursatday=$hoursatturday1-$gio3;
            }
            echo "<td class=\"row1\">$hoursatday:$minutesatday</td>";
            $sql11="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Sunday'";
            $query11=$this->db->query($sql11);
            $hoursunday=0;
            $minutesunday=0;
            $hoursunday1=0;
            $minutesunday1=0;
            foreach($query11->result() as $row11)
            {
                if(substr($row11->starttime,14,2)>substr($row11->endtime,14,2))
                {
                    $phutcn=substr($row11->endtime,14,2)+60-substr($row11->starttime,14,2);
                    if((substr($row11->endtime,11,12)<=13||substr($row11->starttime,11,12)>=13)&&substr($row11->starttime,11,12)!=0)
                    $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2)-1;
                    else
                        $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2)-2;
                }
                else
                {
                    $phutcn=substr($row11->endtime,14,2)-substr($row11->starttime,14,2);
                    if((substr($row11->endtime,11,12)<=13||substr($row11->starttime,11,12)>=13)&&substr($row11->starttime,11,12)!=0)
                    $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2);
                    else
                        $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2)-1;
                }
                
                $minutesunday=$minutesunday+$phutcn;
                $hoursunday=$hoursatturday+$giocn;
                $minutesunday1=$minutesunday%60;
                $hoursunday1=(int)($minutesunday/60)+$hoursunday;
            }
            if($minutesunday1<$phut5)
            {
                $minutesuday=$minutesunday1+60-$phut5;
                $hoursuday=$hoursunday1-1-$gio5;
            }
            else
            {
                $minutesuday=$minutesunday1-$phut5;
                $hoursuday=$hoursunday1-$gio5;
            }
            echo "<td class=\"row1\">$hoursuday:$minutesuday</td>";
            
                echo "</table>
                </tr>";
        echo '</table>';
?>
